package com.stm.bi.report.salesTrend;

import com.stm.base.dto.vo.DictVO;
import com.stm.base.provider.DictionaryProvider;
import com.stm.bi.core.util.DateUtils;
import com.stm.bi.core.util.ETLUtils;
import com.stm.bi.core.util.SqlUtils;
import com.stm.bi.dto.query.DimensionDataGroupQry;
import com.stm.bi.dto.query.GroupField;
import com.stm.bi.dto.query.SalesTrendTableQuery;
import com.stm.bi.dto.query.ods.OdsListQry;
import com.stm.bi.model.DatatablePage;
import com.stm.bi.model.RowData;
import com.stm.bi.model.Title;
import com.stm.bi.report.AbsDatatableQueryExe;
import com.stm.bi.report.util.DimensionPolicyUtils;
import com.stm.bi.repository.dataobject.ods.OdsDeptDO;
import com.stm.bi.repository.dataobject.ods.OdsShopDO;
import com.stm.bi.repository.ods.OdsDeptRepository;
import com.stm.bi.repository.ods.OdsShopRepository;
import com.stm.framework.dto.Response;
import com.stm.saas.api.PolicyDetailRemoteService;
import com.stm.saas.api.ProductConfigRemoteService;
import com.stm.saas.dto.vo.IndexVO;
import com.stm.saas.dto.vo.PolicyDetailVO;
import com.stm.saas.dto.vo.ProductConfigVO;
import com.stm.ucenter.enums.BusinessType;
import com.stm.ucenter.enums.DeptNature;
import com.stm.ucenter.enums.DeptType;
import com.stm.ucenter.enums.ShopStatus;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

import static com.baomidou.mybatisplus.core.toolkit.Constants.DESC;
import static com.stm.bi.constant.GroupTypeConstants.SUM;
import static com.stm.bi.constant.IndexConstants.*;
import static com.stm.bi.constant.SalesTrendConstants.*;
import static com.stm.bi.report.salesTrend.SalesTrendDomainService.getShopFormat;

@Component
public class SalesTrendTableQueryExe extends AbsDatatableQueryExe<SalesTrendTableQuery, DatatablePage> {

    @Resource
    private ProductConfigRemoteService productConfigRemoteService;
    @Resource
    private OdsDeptRepository odsDeptRepository;
    @Resource
    private OdsShopRepository odsShopRepository;
    @Resource
    private DictionaryProvider dictionaryProvider;
    @Resource
    private PolicyDetailRemoteService policyDetailRemoteService;

    @Override
    public DatatablePage query(Long orgId, String orgNo, SalesTrendTableQuery query) {
        if (query.getDimension().matches("^\\d+$")) {
            List<PolicyDetailVO> policyDetailVOS = policyDetailRemoteService.selectByIds(List.of(Long.valueOf(query.getDimension()))).getData();
            query.setDimension(DimensionPolicyUtils.getPolicyByDimension(query.getDimension(), policyDetailVOS));
        }


        Response<ProductConfigVO> config = productConfigRemoteService.selectByOrgId(orgId);
        String salesType = "dealing";
        if(!config.isSuccess()){
            salesType = config.getData().getSalesDefinition();
        }

        List<String> dates = DateUtils.getReportDateList(query.getStartDate(), query.getEndDate(), query.getDateType().getValue());

        String key = query.getDimension().split("_")[0];
        return switch (key){
            case CUSTOMER     -> getCustomerTable(dates, orgNo, salesType, query);
            case ORGANIZATION -> getOrganizationTable(dates, orgNo, salesType, query);
            case PROD         -> getProdTable(dates, orgNo, salesType, query);
            case FORMAT       -> getFormatTable(dates, orgNo, orgId, salesType, query);
            case REGION       -> getRegionTable(dates, orgNo, salesType, query);
            default           -> throw new IllegalStateException("未知维度类型: " + key);
        };

    }


    private DatatablePage getCustomerTable(List<String> dates, String orgNo, String salesType, SalesTrendTableQuery query) {

        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setDeptId(query.getDeptId())
                .setOrgNo(orgNo)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setSalesType(salesType)
                .setDateType(query.getDateType().getValue());
        String sql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        DatatablePage table = new DatatablePage();
        List<RowData> rows = new ArrayList<>();

        List<Title> titles = getTitles("item", "顾客类别", dates, query.getDateType().getValue());
        titles.getLast().setName("合计");

        RowData totalRow = new RowData();
        RowData newRow = new RowData();
        RowData oldRow = new RowData();
        totalRow.put("item", "总计");
        newRow.put("item", "新顾客");
        oldRow.put("item", "老顾客");

        BigDecimal totalSum = BigDecimal.ZERO;
        BigDecimal newSum = BigDecimal.ZERO;
        BigDecimal oldSum = BigDecimal.ZERO;
        for(String date: dates){
            Map map = maps.stream()
                    .filter(m -> Objects.equals(m.get(DATE_FIELD), date))
                    .findAny().orElse(new HashMap());

            BigDecimal newCustomers = map.get(NEW_CUSTOMERS_FILED) == null ? BigDecimal.ZERO : (BigDecimal) map.get(NEW_CUSTOMERS_FILED);
            BigDecimal oldCustomers = map.get(OLD_CUSTOMERS_FILED) == null ? BigDecimal.ZERO : (BigDecimal) map.get(OLD_CUSTOMERS_FILED);
            BigDecimal total = newCustomers.add(oldCustomers);

            newSum = newSum.add(newCustomers);
            oldSum = oldSum.add(oldCustomers);
            totalSum = totalSum.add(total);

            totalRow.put(date, total);
            newRow.put(date, newCustomers);
            oldRow.put(date, oldCustomers);
        }

        totalRow.put("avg", totalSum);
        newRow.put("avg", newSum);
        oldRow.put("avg", oldSum);

        rows.add(totalRow);
        rows.add(newRow);
        rows.add(oldRow);

        table.setDatas(rows);
        table.setTitles(titles);
        table.setTotal(3);

        return table;
    }

    private DatatablePage getOrganizationTable(List<String> dates, String orgNo, String salesType, SalesTrendTableQuery query) {

        List<OdsDeptDO> allDepts = odsDeptRepository.selectList(new OdsListQry().setOrgNo(orgNo));
        List<OdsDeptDO> depts = allDepts.stream()
                .filter(d -> Objects.equals(d.getParentId(), query.getDeptId())
                        && Objects.equals(d.getBusinessAttr(), BusinessType.MK.getValue())
                        && Objects.equals(d.getNature(), DeptNature.LINE.getValue()))
                .toList();

        if(depts.isEmpty()){
            depts = allDepts.stream().filter(d -> d.getId().equals(query.getDeptId())).toList();
        }
        List<Long> deptIds = depts.stream().map(OdsDeptDO::getId).toList();

        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setOrgNo(orgNo)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setSalesType(salesType)
                .setDateType(query.getDateType().getValue())
                .setDeptIds(deptIds)
                .setGroupFields(List.of(GroupField.of(SALES_AMOUNT_FIELD, SUM)))
                .setGroupBy(List.of(DEPT_ID_FIELD))
                .setOrderBy(SALES_AMOUNT_FIELD)
                .setOrderType(DESC);

        String itemSql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));

        List<Map> itemMaps;
        if(deptIds.isEmpty()){
            itemMaps = new ArrayList<>();
        }else {
            itemMaps = jdbcService.selectList(itemSql, new Object[]{});
        }

        List<Map> finalItemMaps = subItemMaps(itemMaps, query);

        List<Long> itemDeptIds = new ArrayList<>();
        for(Map itemMap : finalItemMaps){
            Long deptId = (Long) itemMap.get(DEPT_ID_FIELD);
            OdsDeptDO dept = depts.stream().filter(d -> d.getId().equals(deptId)).findAny().orElse(new OdsDeptDO());
            itemMap.put(DEPT_NAME_FIELD, dept.getName());
            itemDeptIds.add(deptId);
        }

        qry.setDeptIds(itemDeptIds)
                .setGroupFields(null)
                .setGroupBy(null)
                .setOrderBy(null)
                .setOrderType(null)
                .setLimit(null);

        String sql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));

        List<Map> maps;
        if(itemDeptIds.isEmpty()){
            maps = new ArrayList<>();
        }else {
            maps = jdbcService.selectList(sql, new Object[]{});
        }

        if(query.getPageNum() == 1){
            qry.setGroupFields(GROUP_FIELD_MAP.get(ORGANIZATION))
                    .setGroupBy(List.of(DATE_FIELD))
                    .setOtherConditions(null);
            String allSql = SqlUtils.buildSql(qry, TABLE_NAME.get(ORGANIZATION));
            addTotalMap(allSql, null, maps, finalItemMaps, ORGANIZATION, DEPT_ID_FIELD, DEPT_NAME_FIELD, SALES_INDEX_NAME);
        }

        return getTrendTable(dates, query, finalItemMaps, maps, TABLE_INDEX_MAP.get(ORGANIZATION), SALES_AMOUNT_FIELD, List.of(DEPT_ID_FIELD, DEPT_NAME_FIELD), itemMaps.size());
    }

    private DatatablePage getProdTable(List<String> dates, String orgNo, String salesType, SalesTrendTableQuery query) {
        String sumField = INDEX_FIELD_MAP.get(query.getType()).getFieldName();

        List<String> fields = FIELD_MAP.get(query.getDimension());
        String code = fields.getFirst();
        String name = fields.getLast();

        List<GroupField> groupFields = GROUP_FIELD_MAP.get(PROD);

        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setDeptId(query.getDeptId())
                .setOrgNo(orgNo)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setSalesType(salesType)
                .setDateType(query.getDateType().getValue())
                .setGroupFields(groupFields)
                .setGroupBy(fields)
                .setOrderBy(sumField)
                .setOrderType(DESC);

        String itemSql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
        List<Map> itemMaps = jdbcService.selectList(itemSql, new Object[]{});

        if (PROD_TYPE.equals(query.getDimension())) {
            name = STYLE_TYPE_NAME_FIELD;
            List<DictVO> prodTypes = dictionaryProvider.selectList("style_category");
            for(Map map: itemMaps){
                String prodType = (String) map.get(STYLE_TYPE_FIELD);
                String prodTypeName = prodTypes.stream()
                        .filter(d -> d.getValue().equals(prodType))
                        .map(DictVO::getLabel)
                        .findAny().orElse("其他");

                map.put(STYLE_TYPE_NAME_FIELD, prodTypeName);
            }
        }

        List<Map> finalItemMaps = subItemMaps(itemMaps, query);

        List<String> codes = finalItemMaps.stream()
                .map(m ->(String) m.get(code))
                .toList();

        Map<String, Object> otherConditions = new HashMap<>();
        otherConditions.put(code, codes);
        List<String> groups = new ArrayList<>(fields);
        groups.add(DATE_FIELD);
        qry.setGroupBy(groups)
                .setOtherConditions(otherConditions)
                .setOrderBy(null)
                .setOrderType(null)
                .setLimit(null);

        String sql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        if(QTY_FIELD.equals(sumField)){
            qry.setGroupFields(List.of(GroupField.of(INVENTORY_FIELD, SUM)))
                    .setSalesType(null);

            String attdSql = SqlUtils.buildSql(qry, ATTD_TABLE_NAME.get(query.getDimension()));
            List<Map> attdMaps = jdbcService.selectList(attdSql, new Object[]{});
            for(Map map : maps){
                Map attdMap = attdMaps.stream()
                        .filter(m -> Objects.equals(m.get(DATE_FIELD), map.get(DATE_FIELD))
                                && Objects.equals(m.get(code), map.get(code)))
                        .findAny().orElse(new HashMap());

                BigDecimal inventory = attdMap.get(INVENTORY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) attdMap.get(INVENTORY_FIELD);
                BigDecimal qty = map.get(QTY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) map.get(QTY_FIELD);
                BigDecimal rate = inventory.add(qty).doubleValue() == 0 ? BigDecimal.ZERO : qty.multiply(BigDecimal.valueOf(100)).divide(inventory.add(qty), 2, RoundingMode.HALF_UP);
                map.put(DIGESTIBILITY_FIELD, rate);
                map.put(INVENTORY_FIELD, inventory);
            }
        }else {
            for(Map map : maps){
                BigDecimal sales = map.get(SALES_AMOUNT_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) map.get(SALES_AMOUNT_FIELD);
                BigDecimal qty = map.get(QTY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) map.get(QTY_FIELD);
                BigDecimal price = qty.doubleValue() == 0 ? BigDecimal.ZERO : sales.divide(qty, 2, RoundingMode.HALF_UP);

                map.put(PRICE_AVG_FIELD, price);
            }
        }

        if(query.getPageNum() == 1){
            qry.setGroupFields(GROUP_FIELD_MAP.get(PROD))
                    .setGroupBy(List.of(DATE_FIELD))
                    .setOtherConditions(null);
            String allSql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
            String attdAllSql = null;
            if(QTY_FIELD.equals(sumField)){
                qry.setGroupFields(List.of(GroupField.of(INVENTORY_FIELD, SUM)))
                        .setSalesType(null);
                attdAllSql = SqlUtils.buildSql(qry, ATTD_TABLE_NAME.get(query.getDimension()));
            }
            addTotalMap(allSql, attdAllSql, maps, finalItemMaps, PROD, code, name, INDEX_FIELD_MAP.get(query.getType()).getName());
        }

        return getTrendTable(dates, query, finalItemMaps, maps, TABLE_INDEX_MAP.get(PROD + query.getType()), sumField, fields, itemMaps.size());
    }

    private DatatablePage getFormatTable(List<String> dates, String orgNo, Long orgId, String salesType, SalesTrendTableQuery query) {

        List<DictVO> formats;
        if(Objects.equals(FORMAT_CHANNEL, query.getDimension())){
            DictVO dict = new DictVO();
            dict.setLabel("其他");
            dict.setValue("-99");
            formats = List.of(dict);
        }else {
            List<DictVO> dicts = dictionaryProvider.selectList(DICT_MAP.get(query.getDimension()));
            formats = dicts.stream()
                    .peek(d -> d.getChildren().forEach(c ->
                            c.setLabel(d.getLabel() + "-" + c.getLabel())))
                    .flatMap(d -> d.getChildren().stream())
                    .toList();
        }


        List<OdsDeptDO> depts = odsDeptRepository.selectList(new OdsListQry().setOrgId(orgId));
        List<OdsShopDO> shops = odsShopRepository.selectList(new OdsListQry().setOrgId(orgId));

        List<Long> shopIds = depts.stream()
                .filter(d -> d.getType().equals(DeptType.SHOP.getValue())
                        && d.getAncestors().contains("" + query.getDeptId())
                        && d.getStatus().equals(ShopStatus.IN_OPERATION.getValue()))
                .map(OdsDeptDO::getId)
                .toList();

        //获取店铺业态分类MAP
        Map<String, List<Long>> formatMap = shops.stream()
                .filter(s -> shopIds.contains(s.getDeptId()))
                .filter(s -> getShopFormat(query.getDimension()).apply(s) != null)
                .collect(Collectors.groupingBy(
                        getShopFormat(query.getDimension()),
                        Collectors.mapping(
                                OdsShopDO::getDeptId,
                                Collectors.toList()
                        ))
                );

        List<String> fields = FIELD_MAP.get(query.getDimension());
        String code = fields.getFirst();
        String name = fields.getLast();

        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setDeptIds(shopIds)
                .setOrgNo(orgNo)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setSalesType(salesType)
                .setDateType(query.getDateType().getValue())
                .setGroupFields(List.of(GroupField.of(SALES_AMOUNT_FIELD, SUM)))
                .setGroupBy(List.of(DEPT_ID_FIELD));

        String itemSql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
        List<Map> items = jdbcService.selectList(itemSql, new Object[]{});

        List<Map> itemMaps = new ArrayList<>();
        formatMap.forEach((k,v) -> {
            DictVO dict = formats.stream()
                    .filter(d -> d.getValue().equals(k))
                    .findAny().orElse(new DictVO().setLabel("其他"));
            Map<String, Object> itemMap = new HashMap<>();
            itemMap.put(code, k);
            itemMap.put(name, dict.getLabel());
            BigDecimal sales = items.stream()
                    .filter(m -> v.contains((Long)m.get(DEPT_ID_FIELD)))
                    .map(m -> (BigDecimal) m.get(SALES_AMOUNT_FIELD))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

            itemMap.put(SALES_AMOUNT_FIELD, sales);
            itemMaps.add(itemMap);
        });

        itemMaps.sort(Comparator.comparing((Map m) -> (BigDecimal) m.get(SALES_AMOUNT_FIELD)).reversed());

        List<Map> finalItems = subItemMaps(itemMaps, query);

        List<Long> itemShopIds = finalItems.stream()
                .map(m -> (String) m.get(code))
                .flatMap(s -> formatMap.get(s).stream())
                .toList();

        qry.setDeptIds(itemShopIds)
                .setGroupBy(null)
                .setGroupFields(null);

        String sql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        qry.setDeptId(query.getDeptId());

        String attdSql = SqlUtils.buildSql(qry, ATTD_TABLE_NAME.get(query.getDimension()));
        List<Map> attdMaps = new ArrayList<>();
        if(!query.getDimension().equals(FORMAT_CHANNEL)){
            attdMaps = jdbcService.selectList(attdSql, new Object[]{});
        }
        List<Map> finalMaps = new ArrayList<>();
        for(Map map: itemMaps){
            String key = (String)map.get(code);
            for(String date: dates){
                Map finalMap = new HashMap<>();
                finalMap.put(code, key);
                finalMap.put(name, map.get(name));
                finalMap.put(DATE_FIELD, date);

                List<Map> formatMaps = maps.stream()
                        .filter(m -> formatMap.get(key).contains((Long) m.get(DEPT_ID_FIELD)) && m.get(DATE_FIELD).equals(date))
                        .toList();

                BigDecimal sales = formatMaps.stream().map(m -> (BigDecimal) m.get(SALES_AMOUNT_FIELD))
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                BigDecimal daySales = formatMaps.stream().map(m -> (BigDecimal) m.get(AVG_PER_DAY_SALES_FIELD))
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                Map attdMap = attdMaps.stream()
                        .filter(m -> m.get(DATE_FIELD).equals(date) && Objects.equals(m.get(code), key))
                        .findAny().orElse(new HashMap());

                finalMap.put(SALES_AMOUNT_FIELD, sales);
                finalMap.put(AVG_PER_DAY_SALES_FIELD, daySales);

                BigDecimal numberOfTransactions = attdMap.get(NUMBER_OF_TRANSACTIONS_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) attdMap.get(NUMBER_OF_TRANSACTIONS_FIELD);
                BigDecimal numberOfContOrder = attdMap.get(NUMBER_OF_CONT_ORDER_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) attdMap.get(NUMBER_OF_CONT_ORDER_FIELD);
                BigDecimal avgDayNumberOfTransactions = attdMap.get(AVG_DAY_NUMBER_OF_TRANSACTIONS_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) attdMap.get(AVG_DAY_NUMBER_OF_TRANSACTIONS_FIELD);
                BigDecimal perTransaction = numberOfTransactions.doubleValue() == 0 ? BigDecimal.ZERO : sales.divide(numberOfTransactions, 2, RoundingMode.HALF_UP);

                finalMap.put(AVG_DAY_NUMBER_OF_TRANSACTIONS_FIELD, avgDayNumberOfTransactions);
                finalMap.put(NUMBER_OF_TRANSACTIONS_FIELD, numberOfTransactions);
                finalMap.put(NUMBER_OF_CONT_ORDER_FIELD, numberOfContOrder);
                finalMap.put(PER_CUSTOMER_TRANSACTION_FIELD, perTransaction);

                finalMaps.add(finalMap);
            }
        }

        if(query.getPageNum() == 1){
            qry.setGroupFields(GROUP_FIELD_MAP.get(FORMAT))
                    .setGroupBy(List.of(DATE_FIELD))
                    .setOtherConditions(null);
            String allSql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
            addTotalMap(allSql, null, finalMaps, finalItems, FORMAT, code, name, SALES_INDEX_NAME);
        }

        return getTrendTable(dates, query, finalItems, finalMaps, TABLE_INDEX_MAP.get(FORMAT), SALES_AMOUNT_FIELD, fields, items.size());
    }

    private DatatablePage getRegionTable(List<String> dates, String orgNo, String salesType, SalesTrendTableQuery query) {

        List<String> fields = FIELD_MAP.get(query.getDimension());
        String code = fields.getFirst();
        String name = fields.getLast();

        DimensionDataGroupQry qry = new DimensionDataGroupQry()
                .setDeptId(query.getDeptId())
                .setOrgNo(orgNo)
                .setStartDate(dates.getFirst())
                .setEndDate(dates.getLast())
                .setSalesType(salesType)
                .setDateType(query.getDateType().getValue())
                .setGroupFields(GROUP_FIELD_MAP.get(REGION))
                .setGroupBy(fields)
                .setOrderType(DESC)
                .setOrderBy(SALES_AMOUNT_FIELD);

        String itemSql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
        List<Map> itemMaps = jdbcService.selectList(itemSql, new Object[]{});
        List<Map> finalItemMaps = subItemMaps(itemMaps, query);

        for(Map map: finalItemMaps){
            if(Objects.equals(map.get(code), "110100000000")){
                map.put(name, "北京市");
            }

            if(Objects.equals(map.get(code), "120100000000")){
                map.put(name, "天津市");
            }

            if(Objects.equals(map.get(code), "310100000000")){
                map.put(name, "上海市");
            }

            if(Objects.equals(map.get(code), "500100000000")){
                map.put(name, "重庆市");
            }
        }


        List<String> codes = finalItemMaps.stream().map(m -> (String)m.get(code)).toList();
        Map<String, Object> params = new HashMap<>();
        params.put(code, codes);

        qry.setLimit(null)
                .setGroupFields(null)
                .setGroupBy(null)
                .setOrderType(null)
                .setOrderBy(null)
                .setOtherConditions(params);

        String sql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
        List<Map> maps = jdbcService.selectList(sql, new Object[]{});

        if(query.getPageNum() == 1){
            qry.setGroupFields(GROUP_FIELD_MAP.get(REGION))
                    .setGroupBy(List.of(DATE_FIELD))
                    .setOtherConditions(null);
            String allSql = SqlUtils.buildSql(qry, TABLE_NAME.get(query.getDimension()));
            addTotalMap(allSql, null, maps, finalItemMaps, REGION, code, name, SALES_INDEX_NAME);
        }

        return getTrendTable(dates, query, finalItemMaps, maps, TABLE_INDEX_MAP.get(REGION), SALES_AMOUNT_FIELD, fields, itemMaps.size());
    }

    private DatatablePage getTrendTable(List<String> dates,
                                        SalesTrendTableQuery query,
                                        List<Map> itemMaps,
                                        List<Map> maps,
                                        List<IndexVO> indices,
                                        String indexName,
                                        List<String> fields,
                                        Integer total) {
        DatatablePage table = new DatatablePage();
        List<RowData> rows = new ArrayList<>();
        String code = fields.getFirst();
        String name = fields.getLast();
        if (PROD_TYPE.equals(query.getDimension())) {
            name = STYLE_TYPE_NAME_FIELD;
        }

        String itemName = ITEM_NAME_MAP.get(query.getDimension());
        List<Title> titles = getTitles(name, itemName, dates, query.getDateType().getValue());

        String key = query.getDimension().split("_")[0];
        List<Map<String, Integer>> historyDays = DateUtils.getHistoryDateDayList(dates, query.getDateType().getValue(), 2);
        for(Map item: itemMaps){
            RowData row = new RowData();
            row.put(name, Objects.equals(item.get(code), "-99") ? "其他" : item.get(name));

            List<Map> ms = maps.stream()
                    .filter(m -> Objects.equals(m.get(code), item.get(code)))
                    .toList();

            List<RowData> children = new ArrayList<>();
            Map<String, Object> avgMap = getAvgMap(key, indexName, historyDays, ms);
            for (IndexVO index : indices) {
                Map<String, Object> dateMap = new HashMap<>();
                ms.forEach(m -> dateMap.put((String) m.get(DATE_FIELD), m.get(index.getFieldName())));
                if(index.getFieldName().equals(indexName)){
                    setRowData(dates, dateMap, row);
                    row.put("field_name", index.getFieldName());
                    row.put("avg", avgMap.get(index.getFieldName()));
                }else {
                    RowData child = new RowData();
                    child.put(name, index.getName());
                    child.put("field_name", index.getFieldName());
                    setRowData(dates, dateMap, child);
                    child.put("avg", avgMap.get(index.getFieldName()));
                    children.add(child);
                }
            }

            row.setChildren(children);
            rows.add(row);
        }

        table.setDatas(rows);
        table.setTitles(titles);
        table.setCurrent(query.getPageNum());
        table.setSize(query.getPageSize());
        table.setTotal(total);

        return table;
    }

    private Map<String, Object> getAvgMap(String key,
                                          String indexName,
                                          List<Map<String, Integer>> dates,
                                          List<Map> maps) {
        Map<String, Object> res = new HashMap<>();
        if(ORGANIZATION.equals(key) || REGION.equals(key) || FORMAT.equals(key)){
            BigDecimal sales = maps.stream()
                    .map(m -> (BigDecimal) m.get(SALES_AMOUNT_FIELD))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            BigDecimal trans = maps.stream()
                    .map(m -> (BigDecimal) m.get(NUMBER_OF_TRANSACTIONS_FIELD))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

            res.put(SALES_AMOUNT_FIELD, dates.isEmpty() ? BigDecimal.ZERO :sales.divide(BigDecimal.valueOf(dates.size()), 2, RoundingMode.HALF_UP));
            res.put(PER_CUSTOMER_TRANSACTION_FIELD, trans.doubleValue() == 0 ? BigDecimal.ZERO : sales.divide(trans, 2, RoundingMode.HALF_UP));

            Integer days = dates.stream().mapToInt(m -> m.values().stream().reduce(0, Integer::sum)).sum();
            res.put(AVG_DAY_NUMBER_OF_TRANSACTIONS_FIELD, days == 0 ? BigDecimal.ZERO : trans.divide(BigDecimal.valueOf(days), 2, RoundingMode.HALF_UP));

            if(FORMAT.equals(key)){
                OptionalDouble average = maps.stream().map(m -> (BigDecimal) m.get(NUMBER_OF_CONT_ORDER_FIELD))
                        .mapToDouble(BigDecimal::doubleValue).average();
                if(average.isPresent()){
                    res.put(NUMBER_OF_CONT_ORDER_FIELD, BigDecimal.valueOf(average.getAsDouble()).setScale(2, RoundingMode.HALF_UP));
                }

                res.put(AVG_PER_DAY_SALES_FIELD, days == 0 ? BigDecimal.ZERO : sales.divide(BigDecimal.valueOf(days), 2, RoundingMode.HALF_UP));
            }
        }else if(PROD.equals(key)){
            BigDecimal qty = maps.stream()
                    .map(m -> (BigDecimal) m.get(QTY_FIELD))
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

            Integer days = dates.stream().mapToInt(m -> m.values().stream().reduce(0, Integer::sum)).sum();
            if(SALES_AMOUNT_FIELD.equals(indexName)){
                BigDecimal sales = maps.stream()
                        .map(m -> (BigDecimal) m.get(SALES_AMOUNT_FIELD))
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                res.put(SALES_AMOUNT_FIELD, dates.isEmpty() ? BigDecimal.ZERO : sales.divide(BigDecimal.valueOf(dates.size()), 2, RoundingMode.HALF_UP));
                res.put(PRICE_AVG_FIELD, qty.doubleValue() == 0 ? BigDecimal.ZERO : sales.divide(qty, 2, RoundingMode.HALF_UP));
                res.put(AVG_DAY_QTY_FIELD, days == 0 ? BigDecimal.ZERO : qty.divide(BigDecimal.valueOf(days), 2, RoundingMode.HALF_UP));
                res.put(AVG_PER_DAY_SALES_FIELD, days == 0 ? BigDecimal.ZERO : sales.divide(BigDecimal.valueOf(days), 2, RoundingMode.HALF_UP));
            }else {
                BigDecimal inventory = maps.stream()
                        .map(m -> (BigDecimal) m.get(INVENTORY_FIELD))
                        .reduce(BigDecimal.ZERO, BigDecimal::add);

                res.put(INVENTORY_FIELD, dates.isEmpty() ? BigDecimal.ZERO : inventory.divide(BigDecimal.valueOf(dates.size()), 2, RoundingMode.HALF_UP));
                res.put(DIGESTIBILITY_FIELD, inventory.add(qty).doubleValue() == 0 ? BigDecimal.ZERO : qty.multiply(BigDecimal.valueOf(100)).divide(inventory.add(qty), 2, RoundingMode.HALF_UP));
            }
        }

        return res;
    }

    private List<Title> getTitles(String code, String name, List<String> dates, String dateType) {

        List<Title> titles = ETLUtils.getTitles(dates, dateType);
        titles.addFirst(Title.of(code, name));
        titles.add(Title.of("avg", "累计平均"));
        return titles;
    }

    private static void setRowData(List<String> dates, Map<String, Object> map, RowData row) {
        for (String date : dates) {
            row.put(date, map.get(date));
        }
    }

    private void addTotalMap(String allSql,
                             String attdAllSql,
                             List<Map> maps,
                             List<Map> itemMaps,
                             String key,
                             String code,
                             String field,
                             String fieldName) {
        List<Map> allMaps = jdbcService.selectList(allSql, new Object[]{});
        List<Map> attdAllMaps;
        if(attdAllSql != null){
            attdAllMaps = jdbcService.selectList(attdAllSql, new Object[]{});
        }else {
            attdAllMaps = new ArrayList<>();
        }
        allMaps.forEach(m -> {
            m.put(code, "total");
            m.put(field, fieldName + "合计");
            setTotalMap(m, attdAllMaps, key, fieldName);
        });
        maps.addAll(allMaps);
        itemMaps.addFirst(new HashMap(){{
            put(code, "total");
            put(field, fieldName + "合计");
        }});
    }

    private void setTotalMap(Map m, List<Map> attdAllMaps, String key, String fieldName) {
        Map attdMap = attdAllMaps.stream()
                .filter(lm -> Objects.equals(lm.get(key), m.get(key)))
                .findAny().orElse(new HashMap());
        if(fieldName.equals(SALES_INDEX_NAME)){
            BigDecimal sales = m.get(SALES_AMOUNT_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) m.get(SALES_AMOUNT_FIELD);
            if(ORGANIZATION.equals(key) || REGION.equals(key) || FORMAT.equals(key)){
                BigDecimal transactions = m.get(NUMBER_OF_TRANSACTIONS_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) m.get(NUMBER_OF_TRANSACTIONS_FIELD);
                m.put(PER_CUSTOMER_TRANSACTION_FIELD, transactions.doubleValue() == 0 ? BigDecimal.ZERO :  sales.divide(transactions, 2, RoundingMode.HALF_UP));
            }else if(PROD.equals(key)){
                BigDecimal qty = m.get(QTY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) m.get(QTY_FIELD);
                m.put(PRICE_AVG_FIELD, qty.doubleValue() == 0 ? BigDecimal.ZERO :  sales.divide(qty, 2, RoundingMode.HALF_UP));
            }
        }else if(fieldName.equals(QTY_INDEX_NAME)){
            BigDecimal qty = m.get(QTY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) m.get(QTY_FIELD);
            BigDecimal inventory = attdMap.get(INVENTORY_FIELD) == null ? BigDecimal.ZERO : (BigDecimal) attdMap.get(INVENTORY_FIELD);
            m.put(INVENTORY_FIELD, inventory);
            m.put(DIGESTIBILITY_FIELD, qty.add(inventory).doubleValue() == 0 ? BigDecimal.ZERO :  qty.multiply(BigDecimal.valueOf(100)).divide(qty.add(inventory), 2, RoundingMode.HALF_UP));
        }

    }


    private List<Map> subItemMaps(List<Map> itemMaps, SalesTrendTableQuery query) {
        return itemMaps.subList(
                Math.min(itemMaps.size(), Math.max((query.getPageNum() - 1) * query.getPageSize() - 1, 0)),
                Math.min(itemMaps.size(), query.getPageNum() * query.getPageSize() - 1)
        );
    }

    @Override
    public String getReportName() {
        return null;
    }

    @Override
    public boolean syncExport(DatatablePage page) {
        return false;
    }
}
